﻿using EMS.Entities;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace EMS
{
    public partial class Users : DevExpress.XtraEditors.XtraForm
    {
        public const int WM_NCLBUTTONDOWN = 0xA1;
        public const int HT_CAPTION = 0x2;

        [DllImportAttribute("user32.dll")]
        public static extern int SendMessage(IntPtr hWnd,
                         int Msg, int wParam, int lParam);
        [DllImportAttribute("user32.dll")]
        public static extern bool ReleaseCapture();

        string connectionString = ConfigurationManager.ConnectionStrings["OrlcConnect"].ConnectionString;

        List<CustomerView> customers = new List<CustomerView>();

        private int currentUser;
        private int currentCustomer;
        private int staffId;
        private string pass;

        public Users(int staffId)
        {
            InitializeComponent();
            this.staffId = staffId;
        }

        private void LoadList(string searchString)
        {
            List<CustomerView> lst = new List<CustomerView>();
            if (!String.IsNullOrEmpty(searchString))
            {
                lst = customers.Where(s => s.Name.ToLower().Contains(searchString)).ToList();
            }
            else
            {
                lst = customers;
            }
            dgvCustomer.DataSource = lst;
        }

        private void LoadCustomerView()
        {
            customers.Clear();
            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleTransaction trans = connection.BeginTransaction();

                var cmd = new OracleCommand()
                {
                    Connection = connection,
                    CommandText = @"SELECT c.customerid, p.personid, t.typeid, p.name, p.address, p.email, p.phone, c.cmnd, c.inuse, t.name, c.registerplace
                                    FROM customer c, person p, type t
                                    WHERE c.personid = p.personid and t.typeid = c.typeid",
                    CommandType = CommandType.Text,
                    Transaction = trans
                };

                try
                {
                    OracleDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        customers.Add(new CustomerView()
                        {
                            CustomerId = reader.GetInt32(0),
                            PersonId = reader.GetInt32(1),
                            TypeId = reader.GetInt32(2),
                            Name = reader.GetString(3),
                            Address = reader.GetString(4),
                            Email = reader.GetString(5),
                            Phone = reader.GetString(6),
                            CMND = reader.GetString(7),
                            Active = reader.GetInt32(8),
                            TypeName = reader.GetString(9),
                            RegisterPlace = reader.GetInt32(10)
                        });
                    }
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    MessageBox.Show(ex.Message);
                }
                trans.Commit();
            }
            
            cbbActive.Items.Clear();
            cbbActive.Items.Add("Actived");
            cbbActive.Items.Add("Unactived");

            cbbType.Items.Clear();
            cbbType.Items.Add("Home");
            cbbType.Items.Add("Bussiness");

            cbbOffice.Items.Clear();
            cbbOffice.Items.Add("Hai Chau");
            cbbOffice.Items.Add("Thanh Khe");
        }

        private void loadStaff()
        {

            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();

                OracleCommand cmd = connection.CreateCommand();
                OracleTransaction trans = connection.BeginTransaction();
                try
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = String.Format(@"select pe.name, pe.address, pe.phone, pe.email, f.salary, f.hollydayallowance, p.hourrate, s.password
                                            from staff s left outer join fulltimepermanent f on s.staffid = f.staffid 
                                                  left outer join parttimepermanent p on s.staffid = p.staffid
                                                  left outer join person pe on s.personid = pe.personid 
                                            where s.staffid = {0}", staffId);
                    OracleDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        txtSName.Text = reader.GetString(0);
                        txtSAddress.Text = reader.GetString(1);
                        txtSPhone.Text = reader.GetString(2);
                        txtSEmail.Text = reader.GetString(3);
                        pass = reader.GetString(7);
                        lbSalary.Text = reader.GetValue(4) == DBNull.Value ? "null" : String.Format("$ {0} per month", reader.GetFloat(4));
                        lbHollyday.Text = reader.GetValue(5) == DBNull.Value ? "null" : reader.GetInt32(5) + " days";
                        lbRate.Text = reader.GetValue(6) == DBNull.Value ? "null" : String.Format("$ {0} per hour", reader.GetFloat(6));
                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void Users_Load(object sender, EventArgs e)
        {
            LoadCustomerView();
            LoadList("");
            loadStaff();
        }

        private void Users_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                ReleaseCapture();
                SendMessage(Handle, WM_NCLBUTTONDOWN, HT_CAPTION, 0);
            }
        }

        private void txtSearch_TextChanged(object sender, EventArgs e)
        {
            LoadList(txtSearch.Text.Trim());
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void dgvCustomer_SelectionChanged(object sender, EventArgs e)
        {
            int id = -1;
            foreach (DataGridViewRow item in dgvCustomer.SelectedRows)
            {
                id = Int32.Parse(item.Cells[0].Value.ToString());
            }
            if (id != -1)
            {
                cbIsCreate.Checked = false;
                btnCreate.Enabled = false;
                btnSave.Enabled = true;
                CustomerView view = customers.Where(v => v.CustomerId == id).SingleOrDefault();
                txtName.Text = view.Name;
                txtAddress.Text = view.Address;
                txtCmnd.Text = view.CMND;
                txtEmail.Text = view.Email;
                txtPhone.Text = view.Phone;
                if (view.TypeName.ToLower().Equals("home"))
                {
                    cbbType.SelectedItem = "Home";
                }
                else
                {
                    cbbType.SelectedItem = "Bussiness";
                }
                if (view.Active == 1)
                {
                    cbbActive.SelectedItem = "Actived";
                }
                else
                {
                    cbbActive.SelectedItem = "Unactived";
                }
                if (view.RegisterPlace == 1)
                {
                    cbbOffice.SelectedItem = "Hai Chau";
                }
                else
                {
                    cbbOffice.SelectedItem = "Thanh Khe";
                }
                currentCustomer = view.CustomerId;
                currentUser = view.PersonId;
            }
        }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
            int inuse = 0;
            if (cbbActive.SelectedItem == "Actived")
            {
                inuse = 1;
            }
            int typeid = 2;
            if (cbbType.SelectedItem == "Home")
            {
                typeid = 1;
            }
            int office = 1;
            if (cbbOffice.SelectedItem == "Thanh Khe")
            {
                office = 2;
            }
            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();

                OracleCommand cmd = connection.CreateCommand();
                OracleTransaction trans = connection.BeginTransaction();
                try
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = String.Format(@"update person set name = '{0}', address = '{1}', phone = '{2}', email = '{3}' where personid = {4}", txtName.Text, txtAddress.Text, txtPhone.Text, txtEmail.Text, currentUser);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = String.Format(@"update customer set cmnd = '{0}', inuse = {1}, typeid = {2}, registerplace = {3} where customerid = {4}", txtCmnd.Text, inuse, typeid, office, currentCustomer);
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    MessageBox.Show(ex.Message);
                }
            }
            
            LoadCustomerView();
            LoadList("");
            MessageBox.Show("Update successfull");
        }

        private void cbIsCreate_CheckedChanged(object sender, EventArgs e)
        {
            if (cbIsCreate.Checked)
            {
                btnCreate.Enabled = true;
                btnSave.Enabled = false;
                txtName.Text = "";
                txtAddress.Text = "";
                txtCmnd.Text = "";
                txtEmail.Text = "";
                txtPhone.Text = "";
            }
            else
            {
                btnCreate.Enabled = false;
                btnSave.Enabled = true;
            }
        }

        private void btnCreate_Click(object sender, EventArgs e)
        {
            if (txtAddress.Text == "" || txtCmnd.Text == "" || txtEmail.Text == "" || txtName.Text == "" || txtPhone.Text == "")
            {
                MessageBox.Show("All fields are required");
            }
            else
            {
                int inuse = 0;
                if (cbbActive.SelectedItem == "Actived")
                {
                    inuse = 1;
                }
                int typeid = 2;
                if (cbbType.SelectedItem == "Home")
                {
                    typeid = 1;
                }
                int office = 1;
                if (cbbOffice.SelectedItem == "Thanh Khe")
                {
                    office = 2;
                }
                using (var connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    OracleCommand cmd = connection.CreateCommand();
                    OracleTransaction trans = connection.BeginTransaction();
                    try
                    {
                        cmd.Transaction = trans;
                        cmd.CommandText = String.Format(@"DECLARE
                                              Rid INTEGER;
                                            BEGIN
                                              INSERT INTO person( name, address, phone, email )
                                                VALUES('{0}', '{1}', '{2}', '{3}')
                                                RETURNING personid
                                                     INTO Rid;
                                              INSERT INTO customer( cmnd, inuse, typeid, personid, registerplace )
                                                VALUES('{4}', {5}, {6}, Rid, {7});
                                            END;", txtName.Text, txtAddress.Text, txtPhone.Text, txtEmail.Text, txtCmnd.Text, inuse, typeid, office);
                        cmd.ExecuteNonQuery();
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        MessageBox.Show(ex.Message);
                    }
                }
                
                LoadCustomerView();
                LoadList("");
                MessageBox.Show("Create new customer successfully");
            }
        }

        private void btnChangeP_Click(object sender, EventArgs e)
        {
            if (txtOpwd.Text != pass)
            {
                MessageBox.Show("Old password is incorrect");
            }
            else
            {
                if (txtNpwd.Text != txtRpwd.Text)
                {
                    MessageBox.Show("New password must match");
                }
                else
                {
                    using (var connection = new OracleConnection(connectionString))
                    {
                        connection.Open();

                        OracleCommand cmd = connection.CreateCommand();
                        OracleTransaction trans = connection.BeginTransaction();
                        try
                        {
                            cmd.Transaction = trans;
                            cmd.CommandText = String.Format(@"update staff set password = '{0}' where staffid = {1}",
                                txtRpwd.Text, staffId);
                            cmd.ExecuteNonQuery();
                            trans.Commit();
                            MessageBox.Show("Password updated");
                            txtRpwd.Text = String.Empty;
                            txtOpwd.Text = String.Empty;
                            txtNpwd.Text = String.Empty;
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            MessageBox.Show(ex.Message);
                        }
                    }
                }
            }
            
        }

    }
}
